﻿
Public Interface ISerialNoCreator

    Function getSerialNumber(ByVal diplomaType As DiplomaType, ByVal student As Student) As Integer

End Interface

''' <summary>
''' Returns the Serial number for PAS/DAS members.
''' The PAS and DAS share the same numbers.  if a user does not 
''' have a registration number in the old system a new one is created.
''' If a user has a registration number for any DAS/PAS class it is kept.
''' </summary>
Public Class DefaultSerialNoCreator
    Implements ISerialNoCreator

    Private _serialNoCounter As Dictionary(Of Integer, Integer) = New Dictionary(Of Integer, Integer)

    Public Function getSerialNumber(ByVal diplomaType As DiplomaType, ByVal student As IICModel.BusObjects.Student) As Integer Implements ISerialNoCreator.getSerialNumber

        Dim diplomaTypeId As Integer = diplomaType.DiplomaTypeId

        If _serialNoCounter.ContainsKey(diplomaTypeId) = False Then
            _serialNoCounter(diplomaTypeId) = CInt(ModelContext.CurrentDBUtils.getLngValueWithParams( _
                "select max(serialNo) from DiplomaEarned where DiplomaTypeId={0}", _
                    diplomaTypeId))

            'logger.DebugFormat("New Serial Number:{0}", _serialNoCounter(DiplomaTypeId))

        End If

        _serialNoCounter.Item(diplomaTypeId) = _serialNoCounter.Item(diplomaTypeId) + 1

        'increment by one!
        Return _serialNoCounter.Item(diplomaTypeId)

    End Function

End Class

Public Class PasSerialNoCreator
    Implements ISerialNoCreator

    Private _nextRegistrationNumber As Integer

    ''' <summary>
    ''' Returns the current registration number counter.
    ''' </summary>
    Public ReadOnly Property CurrentRegistrationNumberCounter() As Integer
        Get
            Return _nextRegistrationNumber
        End Get
    End Property

    Sub New()
        Dim tmp As Integer = ModelContext.CurrentDBUtils.getLngValue( _
               "select max(a.PAS_REG_NO) from [STGIICDB].[dbo].[MEMBERS] a")

        Dim maxNewSystemRegNoSql As XElement = <sql>
                                        
                   		SELECT    max( a.SerialNo )
                   		FROM         DiplomaEarned AS a INNER JOIN
                   		                      DiplomaType AS b ON a.DiplomaTypeId = b.DiplomaTypeId INNER JOIN
                   		                      DiplomaSuperType AS c ON b.DiplomaSuperTypeId = c.DiplomaSuperTypeId INNER JOIN
                   		                      Student AS s ON a.StudentId = s.StudentId
                   		WHERE    c.DiplomaSuperTypeId IN (2, 5)
                   		</sql>


        Dim tmp2 As Integer = CInt(ModelContext.CurrentDBUtils.getLngValueWithParams( _
                maxNewSystemRegNoSql.Value))

        _nextRegistrationNumber = CInt(IIf(tmp >= tmp2, tmp, tmp2))

    End Sub

    Private _serialNoCache As Dictionary(Of Integer, Integer) = New Dictionary(Of Integer, Integer)

    ''' <summary>
    ''' Creates a new pas registration number, by getting the existing maximum serial number 
    ''' where DiplomaSuperTypeId is PAS or DAS, plus 1.
    ''' However, we need to check the maximum from the old system.
    ''' </summary>
    Private Sub createNextPasRegNumber()

        Me._nextRegistrationNumber = Me._nextRegistrationNumber + 1

    End Sub


    Private Shared Function getExistingRegNo(ByVal student As Student) As Integer

        'step 2: see if reg no does not exist in old database, see if one exists in DiplomaEarned.
        Dim sql = <sql>
           		SELECT     a.SerialNo
           		FROM         DiplomaEarned AS a INNER JOIN
           		                      DiplomaType AS b ON a.DiplomaTypeId = b.DiplomaTypeId INNER JOIN
           		                      DiplomaSuperType AS c ON b.DiplomaSuperTypeId = c.DiplomaSuperTypeId INNER JOIN
           		                      Student AS s ON a.StudentId = s.StudentId
           		WHERE     (c.DiplomaSuperTypeId IN (2, 5)) AND (s.MemberNumber ={0})
           		</sql>
        'now see if the student already has a pas reg no
        Dim pasregno As Integer = CInt(ModelContext.CurrentDBUtils.getLngValueWithParams(sql.Value, student.StudentId))

        Return pasregno

    End Function

    ''' <summary>
    ''' Returns the student PAS/DAS registration number from the old system, if any
    ''' </summary>
    ''' <param name="student"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Private Shared Function getRegNoFromOldSystem(ByVal student As Student) As Integer

        'step 1: see if reg no exists in old database
        Dim pasregno As Integer = CInt(ModelContext.CurrentDBUtils.getLngValueWithParams( _
                       "select a.PAS_REG_NO from [STGIICDB].[dbo].[MEMBERS] a where a.MEMBER_NO={0}", _
                           student.MemberNumber))
        Return pasregno

    End Function

    Public Function getSerialNumber(ByVal diplomaType As DiplomaType, ByVal student As Student) _
                As Integer Implements ISerialNoCreator.getSerialNumber

        'If student.MemberNumber = 2088 Then
        '    Stop
        'End If
        '11	4278	9777	DC	NULL	DC	2013-03-31 19:17:00.000	2013-03-31 19:17:00.000	417	1996-10-01 00:00:00.000	ΠΑΣ - ΒΑΣΙΚΟ	ΙΦΙΓΕΝΕΙΑ	ΚΑΡΙΒΑΛΛΗ	NULL	Π690302	1	2088
        If _serialNoCache.ContainsKey(student.StudentId) = False Then

            Dim pasRegNo As Integer = getRegNoFromOldSystem(student)

            If pasRegNo = 0 Then
                pasRegNo = getExistingRegNo(student)
            End If

            If pasRegNo = 0 Then
                'lastly if registration number does not exist in old/new system, create a new one
                Call Me.createNextPasRegNumber()
                pasRegNo = Me._nextRegistrationNumber
            End If

            _serialNoCache.Item(student.StudentId) = pasRegNo

        End If

        'if we did not find a pas reg no then see if we have one in the dictionary

        Return _serialNoCache.Item(student.StudentId)

    End Function
End Class
